package com.css.fxfzysx.modules.specialMapAnalysis.repository.impl;

import com.css.fxfzysx.modules.lifeline.entity.BridgeEntity;
import com.css.fxfzysx.modules.lifeline.entity.DxPipeNetworkEntity;
import com.css.fxfzysx.modules.lifeline.entity.RoadEntity;
import com.css.fxfzysx.modules.specialMapAnalysis.repository.SpecialRepository;
import com.css.fxfzysx.modules.specialMapAnalysis.vo.ResultVo;
import com.css.fxfzysx.modules.vaolEngineering.entity.ContinuousWeldingEntity;
import com.css.fxfzysx.modules.vaolEngineering.entity.MtolProjectEntity;
import com.css.fxfzysx.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Repository
public class SpecialRepositoryImpl implements SpecialRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public List<DxPipeNetworkEntity> dxPipeNetworkData(String intensity) {//ST_AsText(geom) geom,
        StringBuilder sql = new StringBuilder("select cdpn_id,damage_extent,intensity,year from czt_dx_pipe_network where del_flag= '0' and year is not null and intensity is not null");
        if(!PlatformObjectUtils.isEmpty(intensity)){
            sql.append(" and intensity = '"+ intensity+"'");
        }
        List<DxPipeNetworkEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DxPipeNetworkEntity.class));
        return list;
    }

    @Override
    public List<ResultVo> mtolProjectDxData(String dIntensity) {
        StringBuilder sql = new StringBuilder(" SELECT a.break_state breakState,b.years,b.fortification,count(*) FROM czt_continuous_welding_method a,czt_main_table_of_lifeline_project b " +
                "where a.del_flag='0' and b.del_flag='0' and a.project_id=b.main_id and a.project_id in ");
        StringBuilder sql2 = new StringBuilder("(SELECT main_id FROM czt_main_table_of_lifeline_project WHERE del_flag='0' and business_object='地下管网'");
        StringBuilder groupSql = new StringBuilder("GROUP BY a.break_state,b.years,b.fortification ORDER BY b.fortification,b.years,count");
        if(!PlatformObjectUtils.isEmpty(dIntensity)){
            sql2.append(" and fortification = '"+ dIntensity+"')");
        }else{
            sql2.append(" and fortification !='未设防' )");
        }
        List<ResultVo> list = jdbcTemplate.query(sql.append(sql2).append(groupSql).toString(), new BeanPropertyRowMapper<>(ResultVo.class));
        return list;
    }


    @Override
    public boolean updateDxDamageExtentByIntensityAndYear(String damageExtent, String intensity, String year) {
        String sql = "update czt_dx_pipe_network set damage_extent=? where intensity=? and year=?";
        return jdbcTemplate.update(sql, damageExtent, intensity, year) > 0;
    }

    @Override
    public boolean updateBridgeDamageExtentByIntensityAndYear(String damageExtent, String intensity, String year) {
        String sql = "update czt_bridge_line set damage_extent=? where intensity=? and build_year=?";
        return jdbcTemplate.update(sql, damageExtent, intensity, year) > 0;
    }

    @Override
    public List<BridgeEntity> bridgeLineData(String intensity) {
        StringBuilder sql = new StringBuilder("select cb_id,damage_extent,intensity,build_year from czt_bridge_line where del_flag= '0' and build_year is not null and intensity is not null");
        if(!PlatformObjectUtils.isEmpty(intensity)){
            sql.append(" and intensity = '"+ intensity+"'");
        }
        List<BridgeEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BridgeEntity.class));
        return list;
    }

    @Override
    public List<RoadEntity> roadData(String intensity) {
        StringBuilder sql = new StringBuilder("select cr_id,damage_extent,fortification from czt_road where del_flag= '0' and fortification is not null");
        if(!PlatformObjectUtils.isEmpty(intensity)){
            sql.append(" and fortification = '"+ intensity+"'");
        }
        List<RoadEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(RoadEntity.class));
        return list;
    }

    @Override
    public boolean updateRoadDamageExtentByIntensity(String damageExtent, String intensity) {
        String sql = "update czt_road set damage_extent=? where fortification=? ";
        return jdbcTemplate.update(sql, damageExtent, intensity) > 0;
    }

    @Override
    public List<ResultVo> mtolProjectBridgeData(String dIntensity) {
        StringBuilder sql = new StringBuilder("SELECT a.seismic_damage_grade breakState,a.years,a.fortification,count(*) FROM czt_bridge_vulnerability a");
        StringBuilder whereSql = new StringBuilder(" where a.del_flag='0' and a.seismic_damage_grade is not null");
        StringBuilder groupSql = new StringBuilder(" GROUP BY a.seismic_damage_grade,a.years,a.fortification ORDER BY a.fortification,a.years,count");
        if(!PlatformObjectUtils.isEmpty(dIntensity)){
            whereSql.append(" and fortification = '"+ dIntensity+"')");
        }else{
            whereSql.append(" and fortification != '0'");
        }
        List<ResultVo> list = jdbcTemplate.query(sql.append(whereSql).append(groupSql).toString(), new BeanPropertyRowMapper<>(ResultVo.class));
        return list;
    }

    @Override
    public List<ResultVo> mtolProjectRoadData(String dIntensity) {
        StringBuilder sql = new StringBuilder("SELECT a.seismic_damage_grade breakState,b.years,b.fortification,count(*) FROM czt_road_vulnerability a,czt_main_table_of_lifeline_project b " +
                " where a.del_flag='0' and b.del_flag='0' and a.project_id=b.main_id and a.project_id in  ");
        StringBuilder sql2 = new StringBuilder("(SELECT main_id FROM czt_main_table_of_lifeline_project WHERE del_flag='0' and business_object='道路'");
        StringBuilder groupSql = new StringBuilder("GROUP BY a.seismic_damage_grade,b.years,b.fortification ORDER BY b.fortification,b.years,count");
        if(!PlatformObjectUtils.isEmpty(dIntensity)){
            sql2.append(" and fortification = '"+ dIntensity+"')");
        }else{
            sql2.append(")");
        }
        List<ResultVo> list = jdbcTemplate.query(sql.append(sql2).append(groupSql).toString(), new BeanPropertyRowMapper<>(ResultVo.class));
        return list;
    }
}
